package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import to.Veiculo;
import to.Vinculo;

import com.mysql.jdbc.Statement;

import dao.util.OperacoesBasicasDAO;
import factory.ConnectionFactory;

public class VeiculoDAO {
	private final String TABELA = " tb_veiculos ";
	private final String COLUM = " id, marca, modelo, placa, cor ";
	private final String COLUM_INSERT = " marca, modelo, placa, cor ";
	
	private List<Veiculo> execute(String sql) {

		List<Veiculo> veiculos = new ArrayList<Veiculo>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			conn = ConnectionFactory.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next()){
				Veiculo v = new Veiculo();
				
				v.setId(rs.getInt("id"));
				v.setMarca(rs.getString("marca"));
				v.setModelo(rs.getString("modelo"));
				v.setPlaca(rs.getString("placa"));
				v.setCor(rs.getString("cor"));

				veiculos.add(v);
			}
		} catch (Exception e) {
			System.out.println("Erro: "+e);
		}finally{
			if(conn != null){ try { conn.close(); } catch (SQLException e) {} }
			if(stmt != null){ try { stmt.close(); } catch (SQLException e) {} }
			if(rs != null){ try { rs.close(); } catch (SQLException e) {} }
		}

		return veiculos;
	
	}
	
	public List<Veiculo> getAll(){
		
		List<Veiculo> veiculos = new ArrayList<Veiculo>();
		
		String sql = "SELECT "+ COLUM +" FROM "+TABELA+" ORDER BY marca";
		
		veiculos = execute(sql);
		
		return veiculos;
	}
	
	public void inserirVeiculo(Veiculo v){
		Connection conn = null;
		Statement statement = null;
		try{
			conn = ConnectionFactory.getConnection();
			
			String sql = "INSERT INTO "+TABELA+" ( "+ COLUM_INSERT +" ) VALUES ( ";
			sql += " '"+v.getMarca()+"', ";
		    sql += " '"+v.getModelo()+"', ";
			sql += " '"+v.getPlaca()+"', ";
			sql += " '"+v.getCor()+"' ";
			sql += " ) ";
			
		    statement = (Statement) conn.createStatement();
		    statement.executeUpdate(sql);
		        
		}catch(Exception e){
			System.out.println("Erro: "+e);
		}finally{
			if(conn != null){
				try { conn.close(); } catch (SQLException e) {}
			}
			if(statement != null){
				try { statement.close(); } catch (SQLException e) {}
			}
		}
		
	}

	public Veiculo getVeiculoPor(String placa) {

		Veiculo vei = null;

		List<Veiculo> veiculos = new ArrayList<Veiculo>();
		try{
			String sql =  "SELECT "+ COLUM +" FROM "+ TABELA + " WHERE PLACA = '"+placa+"'";
			veiculos = execute(sql);
			if(!veiculos.isEmpty()){
				vei = veiculos.get(0);
			}
		} catch (Exception e) {
			System.out.println("Erro: "+e);
		}

		return vei;
	
	}

	public List<Veiculo> getVeiculoPor(List<Vinculo> vinculos) {
		
		List<Veiculo> veiculos = new ArrayList<Veiculo>();
		
		try{
			String sql = " SELECT "+ COLUM +" FROM "+ TABELA + " WHERE ";

			List<String> ids = new ArrayList<String>();
			for (Vinculo v : vinculos) {
				ids.add(""+v.getIdVeiculo());
			}
			
			sql += new OperacoesBasicasDAO().criaClausulaIn(" ID ", ids);
			
			veiculos = execute(sql);
			
		} catch (Exception e) {
			System.out.println("Erro: "+e);
		}

		return veiculos;
	}

	

	
}
